﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using Dapper;

namespace Card.Make.Pro.Application.Dapper
{
    /// <summary>
    /// 操作客户端
    /// </summary>
    public class DapperClient
    {

        /// <summary>
        /// 默认超时时间(秒数)
        /// </summary>
        public const int DEFAULTTIMEOUT = 60;

        /// <summary>
        /// 当前连接配置
        /// </summary>
        public Config CurrentConnectionConfig { get; set; }


        /// <summary>
        /// 初始化
        /// </summary>
        /// <param name="config">数据库连接配置</param>
        public DapperClient(Config config) { CurrentConnectionConfig = config; }

        /// <summary>
        /// 数据库连结
        /// </summary>

        IDbConnection _connection = null;
        /// <summary>
        /// db connection
        /// </summary>
        public IDbConnection Connection
        {
            get
            {
                switch (CurrentConnectionConfig.DbType)
                {
                    case DbStoreType.SqlServer:
                        _connection = new SqlConnection(CurrentConnectionConfig.ConnectionString);
                        break;
                    default:
                        throw new Exception("未指定数据库类型！");
                }
                return _connection;
            }
        }


        /// <summary>
        /// 获取列表
        /// </summary>
        /// <typeparam name="T">返回的结果类型</typeparam>
        /// <param name="sql">查询执行的SQL语句</param>
        /// <param name="param">要传递参数</param>
        /// <param name="cmdTimeout">默认超时时间(秒数)，默认为60秒</param>
        /// <param name="buffered">是否在内存中缓冲结果（一次性取完断开连接），默认为“true”。如果需要一次性取多笔数据，可设置为“false”</param>
        /// <param name="cmdType">执行的命令类型</param>
        /// <returns>泛型类结果列表</returns>
        public IEnumerable<T> Query<T>(string sql, dynamic param = null, int? cmdTimeout = DEFAULTTIMEOUT, bool buffered = true, CommandType? cmdType = null) where T : class
        {
            using (IDbConnection conn = Connection)
            {
                return conn.Query<T>(sql, (object)param, null, buffered, cmdTimeout, cmdType);
            }
        }
        /// <summary>
        /// 获取dynamic列表
        /// </summary>
        /// <param name="sql">查询执行的SQL语句</param>
        /// <param name="param">要传递参数</param>
        /// <param name="cmdTimeout">默认超时时间(秒数)，默认为60秒</param>
        /// <param name="buffered">是否在内存中缓冲结果（一次性取完断开连接），默认为“true”。如果需要一次性取多笔数据，可设置为“false”</param>
        /// <param name="cmdType">执行的命令类型</param>
        /// <returns>dynamic结果列表</returns>
        public IEnumerable<dynamic> QueryDynamics(string sql, dynamic param = null, int? cmdTimeout = DEFAULTTIMEOUT, bool buffered = true, CommandType? cmdType = null)
        {
            using (IDbConnection conn = Connection)
            {
                return conn.Query(sql, (object)param, null, buffered, cmdTimeout, cmdType);
            }
        }

        /// <summary>
        /// 执行SQL返回一个对象
        /// </summary>
        /// <param name="sql">查询执行的SQL语句</param>
        /// <param name="param">要传递参数</param>
        /// <param name="cmdTimeout">默认超时时间(秒数)，默认为60秒</param>
        /// <param name="buffered">是否在内存中缓冲结果（一次性取完断开连接），默认为“true”。如果需要一次性取多笔数据，可设置为“false”</param>
        /// <param name="cmdType">执行的命令类型</param>
        /// <returns>对象</returns>
        public T QueryFirst<T>(string sql, dynamic param = null, int? cmdTimeout = DEFAULTTIMEOUT, bool buffered = true, CommandType? cmdType = null)
        {
            using (IDbConnection conn = Connection)
            {
                return conn.Query<T>(sql, (object)param, null, buffered, cmdTimeout, cmdType).FirstOrDefault<T>();
            }
        }


        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <param name="sql">待执行的SQL语句</param>
        /// <param name="param">要传递参数</param>
        /// <param name="cmdTimeout">默认超时时间(秒数)，默认为60秒</param>
        /// <param name="tran">事务</param>
        /// <param name="cmdType">执行的命令类型</param>
        /// <returns>受影响的行数</returns>
        public int ExecuteNonQuery(string sql, dynamic param = null, int? cmdTimeout = DEFAULTTIMEOUT, IDbTransaction tran = null, CommandType? cmdType = null)
        {
            using (IDbConnection conn = Connection)
            {
                return conn.Execute(sql, (object)param, tran, cmdTimeout, cmdType);
            }
        }

        /// <summary>
        /// 执行sql语句，并返回单个数值
        /// </summary>
        /// <param name="sql">待执行的SQL语句</param>
        /// <param name="param">要传递参数</param>
        /// <param name="cmdTimeout">默认超时时间(秒数)，默认为60秒</param>
        /// <param name="tran">事务</param>
        /// <param name="cmdType">执行的命令类型</param>
        /// <returns>第一个单元格</returns>
        public object ExecuteScalar(string sql, dynamic param = null, int? cmdTimeout = DEFAULTTIMEOUT, IDbTransaction tran = null, CommandType? cmdType = null)
        {
            using (IDbConnection conn = Connection)
            {
                return conn.ExecuteScalar(sql, (object)param, tran, cmdTimeout, cmdType);
            }
        }

        /// <summary>
        /// 执行sql语句，并返回DataTable
        /// </summary>
        /// <param name="sql">待执行的SQL语句</param>
        /// <param name="param">要传递参数</param>
        /// <param name="cmdTimeout">默认超时时间(秒数)，默认为60秒</param>
        /// <param name="tran">事务</param>
        /// <param name="cmdType">执行的命令类型</param>
        /// <returns>Data Table</returns>
        public DataTable ExecuteDataTable(string sql, dynamic param = null, int? cmdTimeout = DEFAULTTIMEOUT, IDbTransaction tran = null, CommandType? cmdType = null)
        {
            using (IDbConnection conn = Connection)
            {
                //这边用Dapper的ExecuteReader，统一了函数参数写法，不用使用SqlParameter。
                using (var reader = conn.ExecuteReader(sql, (object)param, tran, cmdTimeout, cmdType))
                {
                    var dataTable = DataReaderToDataTable(reader);
                    return dataTable;
                }
            }
        }

        /// <summary>
        ///  执行sql语句，并返回DataSet
        /// </summary>
        /// <param name="sql">待执行的SQL语句</param>
        /// <param name="param">要传递参数</param>
        /// <param name="cmdTimeout">默认超时时间(秒数)，默认为60秒</param>
        /// <param name="tran">事务</param>
        /// <param name="cmdType">执行的命令类型</param>
        /// <returns>Data Set</returns>
        public DataSet ExecuteDataSet(string sql, dynamic param = null, int? cmdTimeout = DEFAULTTIMEOUT, IDbTransaction tran = null, CommandType? cmdType = null)
        {
            using (IDbConnection conn = Connection)
            {
                DataSet dataSet = new DataSet();
                //这边用Dapper的ExecuteReader，统一了函数参数写法，不用使用SqlParameter。
                using (var reader = conn.ExecuteReader(sql, (object)param, tran, cmdTimeout, cmdType))
                {
                    do
                    {
                        var dataTable = DataReaderToDataTable(reader);
                        dataSet.Tables.Add(dataTable);
                    }
                    while (reader.NextResult());
                    return dataSet;
                }
            }
        }

        /// <summary>
        /// 根据DataReader生成DataTable，轻量级。      
        /// </summary>
        /// <returns></returns>
        public static DataTable DataReaderToDataTable(IDataReader reader)
        {
            DataTable dataTable = new DataTable();
            int fieldCount = reader.FieldCount;
            for (int i = 0; i <= fieldCount - 1; i++)
            {
                dataTable.Columns.Add(reader.GetName(i), reader.GetFieldType(i));
            }
            dataTable.BeginLoadData();
            object[] fieldValues = new object[fieldCount];
            while (reader.Read())
            {
                reader.GetValues(fieldValues);
                dataTable.LoadDataRow(fieldValues, true);
            }
            dataTable.EndLoadData();
            return dataTable;
        }
    }
}
